/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Datos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author RODRIGO
 */
public class Conexion {
    public String bd = "dbGastroSoft";
    public String login = "root";
    public String password = "10154762";
    public String url = "jdbc:mysql://localhost:3307/" + bd;
    public static Connection conn = null;

    public Conexion() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            System.out.println("conexion exitosa");
        } catch (SQLException e) {
            System.out.println(e);
        } catch (ClassNotFoundException e) {
            System.out.println(e);
        }
    }

    public static Connection getConnection() {
        return conn;
    }

    public void desconectar() {
        conn = null;
    }
    
    public static ResultSet executeQuery(Connection connection, String query, HashMap<String, Object> parametros) throws SQLException {
        ResultSet resultSet = null;

        if (connection != null && !connection.isClosed()) {
            PreparedStatement ps = connection.prepareStatement(query);
            if (parametros != null) {
                for (Map.Entry<String, Object> parametro : parametros.entrySet()) {
                    ps.setObject(Integer.parseInt(parametro.getKey()), parametro.getValue());
                }
            }
            resultSet = ps.executeQuery();
        }

        return resultSet;
    }
    
    public static Object[][] getDatos(String sql){
        try {
            ResultSet res=Conexion.executeQuery(Conexion.conn, sql, null);
            res.last();
            Object[][]datos=new Object[res.getRow()][1];
            res.beforeFirst();
            int i =0;
            while(res.next()){
              datos[i][0]=res.getObject(1);
              i++;
            }
            return  datos;
        } catch (Exception e) {
            System.out.println("error al obtener datos para cargar arbol "+e);
        }
        return null;
        
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static Object [][] select(String table, String fields, String where){
      int registros = 0;
      String colname[] = fields.split(",");

      //Consultas SQL
      String q ="SELECT " + fields + " FROM " + table;
      String q2 = "SELECT count(*) as total FROM " + table;
      if(where!=null)
      {
          q+= " WHERE " + where;
          q2+= " WHERE " + where;
      }
      //obtenemos la cantidad de registros existentes en la tabla
      try{
         PreparedStatement pstm = conn.prepareStatement(q2);
         ResultSet res = pstm.executeQuery();
         res.next();
         registros = res.getInt("total");
         res.close();
      }catch(SQLException e){
         System.out.println(e);
      }
    //se crea una matriz con tantas filas y columnas que necesite
    Object[][] data = new String[registros][fields.split(",").length];
    //realizamos la consulta sql y llenamos los datos en la matriz "Object"
      try{
         PreparedStatement pstm = conn.prepareStatement(q);
         ResultSet res = pstm.executeQuery();
         int i = 0;
         while(res.next()){
            for(int j=0; j<=fields.split(",").length-1;j++){
                data[i][j] = res.getString( colname[j].trim() );
            }
            i++;         }
         res.close();
          }catch(SQLException e){
         System.out.println(e);
    }
    return data;
 }
    
}
